package com.ce.pms.renovation.dao.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.pms.renovation.dao.IInspectionRequisitionDao;
import com.ce.pms.renovation.model.InspectionRequisitionDO;
import com.ce.pms.renovation.vo.InspectionRequisitionQueryConditionVO;
import com.ce.util.BlankUtil;
import com.ce.util.DateUtil;
import com.sitechasia.webx.core.support.Page;
/**
 * 装修验收申请的DAO实现类
 * @author 许心炜
 *
 */
/**
 * @author 许心炜
 *
 */
public class InspectionRequisitionDaoImpl extends PmsBaseDaoImpl<InspectionRequisitionDO> implements IInspectionRequisitionDao{

	/**
	 * 功能描述:  高级查询装修验收申请记录列表<br>
	 * @param queryConditionVO
	 * @return
	 * Create author:许心炜 <br>
	 * Create on:2008-12-26  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public Page queryInspectionRequisitions(InspectionRequisitionQueryConditionVO queryConditionVO) {

		Map<String,Object> paramMap = new HashMap<String,Object>();
		StringBuffer strBuf = new StringBuffer(" select ir.INSPECTION_REQUISITION_ID,ir.INSPECTE_DATE,ir.CREATOR,ir.SYS_DATE,ir.INPECTION_STATE,ins.RESULT,lic.LICENCE_NUMBER,req.CLIENT_NAMES,hm.HOUSE_NUMBER,lic.RENOVATE_LICENCE_ID,secu.USERCODE ");
		strBuf.append(" from RM_INSPECTION_REQUISITION ir left join RM_INSPECTION ins on (ir.`INSPECTION_REQUISITION_ID` = ")
		.append(" ins.`INSPECTION_REQUISITION_ID` and ins.`INSPECTION_TYPE` = '001') join `RM_RENOVATE_LICENCE` lic on lic.`RENOVATE_LICENCE_ID` = ")
		.append(" ir.`RENOVATE_LICENCE_ID` join `RM_RENOVATE_REQUISITION` req on req.`RENOVATE_REQUISITION_ID` = ")
		.append(" lic.`RENOVATE_REQUISITION_ID` join `HM_HOUSE` hm on hm.`HOUSE_ID` = req.`HOUSE_ID` join SEC_USER secu on secu.USER_ID = ir.CREATOR ")
		.append(" join HM_EMPLOYEE emp on emp.EMPLOYEE_ID=secu.EMPLOYEE_ID where 1=1 ");

//		if(!BlankUtil.isBlank(queryConditionVO.getAnnalMan())) {
//			HmEmployeeDomain hmEmployeeDomain = organizationalStructureDaoImpl.queryUserByUserCode(queryConditionVO.getAnnalMan());
//			queryConditionVO.setAnnalManId(hmEmployeeDomain.getSesUser().getUserId());
//		}

		strBuf.append(constructionConditions(queryConditionVO,paramMap));
		strBuf.append(constructionSort(queryConditionVO));
		Page page = pagedSqlNameQueryByPageNo(strBuf.toString(),
				queryConditionVO.getPageNo(),
				queryConditionVO.getPageSize(), paramMap);
		page.setResult(assembleInspectionRequisitionConditionO2V(page.getResult()));
		return page;
	}

	/** 功能描述:  高级查询的O2V<br>
	 * @param result
	 * @return
	 * Create author:许心炜 <br>
	 * Create on:2008-12-29  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	*/
	private List<InspectionRequisitionQueryConditionVO> assembleInspectionRequisitionConditionO2V(List<Object[]> result){
		List<InspectionRequisitionQueryConditionVO> temp=new ArrayList<InspectionRequisitionQueryConditionVO>();
		for(Object[] item : result){
//			HmEmployeeDomain hmEmployeeDomain = organizationalStructureDaoImpl.queryUserByUserId(((BigInteger)item[2]).longValue());
			InspectionRequisitionQueryConditionVO inspectionRequisitionQueryConditionVO=new InspectionRequisitionQueryConditionVO();
			inspectionRequisitionQueryConditionVO.setKeyword(((BigInteger)item[0]).longValue());//验收申请主键
			inspectionRequisitionQueryConditionVO.setInspectionDate((Date)item[1]);//申请验收时间
			inspectionRequisitionQueryConditionVO.setInspectionDateStr(DateUtil.convertDateToStr((Date)item[1], "yyyy-MM-dd HH时"));
			inspectionRequisitionQueryConditionVO.setAnnalManId(((BigInteger)item[2]).longValue());//记录人ID
			inspectionRequisitionQueryConditionVO.setAnnalDate((Date)item[3]);//记录时间
			inspectionRequisitionQueryConditionVO.setReinspectionResult((String)item[4]);//复验结果
			inspectionRequisitionQueryConditionVO.setFirstInspectionResult((String)item[5]);//初验结果
			inspectionRequisitionQueryConditionVO.setRenovateLicenceNO((String)item[6]);//装修许可证
			inspectionRequisitionQueryConditionVO.setOwner((String)item[7]);//业主
			inspectionRequisitionQueryConditionVO.setHouseNumber((String)item[8]);//房号
			inspectionRequisitionQueryConditionVO.setRenovateLicenceID(((BigInteger)item[9]).longValue());//许可证ID
			inspectionRequisitionQueryConditionVO.setAnnalMan((String)item[10]);//记录人
			temp.add(inspectionRequisitionQueryConditionVO);
		}
		return temp;
	}

	/** 功能描述:  构造查询条件<br>
	 * @param queryConditionVO
	 * @param paramMap
	 * @return
	 * Create author:许心炜 <br>
	 * Create on:2008-12-26  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	*/
	private String constructionConditions(InspectionRequisitionQueryConditionVO queryConditionVO,Map<String,Object> paramMap){
		StringBuffer hql=new StringBuffer();

		//楼栋id
		if(!BlankUtil.isBlank(queryConditionVO.getBuildingId())){
			hql.append(" and hm.BUILDING_ID=:buildingId ");
			paramMap.put("buildingId", queryConditionVO.getBuildingId());
		}

		//记录人Id
		if(!BlankUtil.isBlank(queryConditionVO.getAnnalManId())){
			hql.append(" AND ir.CREATOR=:annalManId ");
			paramMap.put("annalManId", queryConditionVO.getAnnalManId());
		}

		//记录人
		if(!BlankUtil.isBlank(queryConditionVO.getAnnalMan())){
			hql.append(" AND secu.USERCODE like ").append(":annalMan ");
			paramMap.put("annalMan", "%" + queryConditionVO.getAnnalMan() + "%");
		}

		//房号
		if(!BlankUtil.isBlank(queryConditionVO.getHouseNumber())){
			hql.append(" AND hm.HOUSE_NUMBER like ").append(":houseNumber");
			paramMap.put("houseNumber", "%" + queryConditionVO.getHouseNumber().trim() + "%");
		}

		//初验结果
		if(!BlankUtil.isBlank(queryConditionVO.getFirstInspectionResult())){
			hql.append(" AND ins.RESULT=:firstInspectionResult ");
			paramMap.put("firstInspectionResult", queryConditionVO.getFirstInspectionResult());
		}

		//业主
		if(!BlankUtil.isBlank(queryConditionVO.getOwner())){
			hql.append(" AND req.CLIENT_NAMES like ").append(":owner");
			paramMap.put("owner", "%" + queryConditionVO.getOwner() + "%");
		}

		//复验结果
		if(!BlankUtil.isBlank(queryConditionVO.getReinspectionResult())){
			hql.append(" AND ir.INPECTION_STATE =:reinspectionResult");
			paramMap.put("reinspectionResult", queryConditionVO.getReinspectionResult());
		}

		//许可证号
		if(!BlankUtil.isBlank(queryConditionVO.getRenovateLicenceNO())){
			hql.append(" AND lic.LICENCE_NUMBER like ").append(":renovateLicenceNO");
			paramMap.put("renovateLicenceNO", "%" + queryConditionVO.getRenovateLicenceNO() + "%");
		}

		//申请验收开始时间
		if(!BlankUtil.isBlank(queryConditionVO.getBeginInspectionDate())){
			hql.append(" AND  ir.INSPECTE_DATE >=:beginInspectionDate ");
			paramMap.put("beginInspectionDate", queryConditionVO.getBeginInspectionDate());
		}

		//申请验收结束时间
		if(!BlankUtil.isBlank(queryConditionVO.getEndInspectionDate())){
			hql.append(" AND  ir.INSPECTE_DATE < ADDDATE(:endInspectionDate,1) ");
			paramMap.put("endInspectionDate", queryConditionVO.getEndInspectionDate());
		}

		//记录开始时间
		if(!BlankUtil.isBlank(queryConditionVO.getBeginAnnalDate())){
			hql.append(" AND ir.SYS_DATE >=:beginAnnalDate ");
			paramMap.put("beginAnnalDate", queryConditionVO.getBeginAnnalDate());
		}

		//记录结束时间
		if(!BlankUtil.isBlank(queryConditionVO.getEndAnnalDate())){
			hql.append(" AND ir.SYS_DATE < ADDDATE(:endAnnalDate,1) ");
			paramMap.put("endAnnalDate", queryConditionVO.getEndAnnalDate());
		}

		return hql.toString();
	}

	/** 功能描述:  构造排序条件<br>
	 * @param queryConditionVO
	 * @return
	 * Create author:许心炜 <br>
	 * Create on:2008-12-26  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	*/
	private String constructionSort(InspectionRequisitionQueryConditionVO queryConditionVO) {
		StringBuilder result=new StringBuilder(" order by ");

		if(queryConditionVO.getOrderValue().equalsIgnoreCase("")){//点击查询时和点击保存按钮时
			result.append(" ir.SYS_DATE desc");
		}else if(queryConditionVO.getOrderValue().equalsIgnoreCase("inspectionDateStr")
				&&  queryConditionVO.getOrderKey().equalsIgnoreCase("asc")){//点击排序时，按申请验收日期
			result.append(" ir.INSPECTE_DATE asc ");
		}else if(queryConditionVO.getOrderValue().equalsIgnoreCase("inspectionDateStr")
				&&  queryConditionVO.getOrderKey().equalsIgnoreCase("desc")){//点击排序时，按申请验收日期
			result.append(" ir.INSPECTE_DATE desc ");
		}

		return result.toString();
	}

	/** 功能描述: 根据ID获得装修验收申请详细信息 <br>
	 * @param inspectionRequisitionId - 装修验收申请ID
	 * @return
	 * Create author:许心炜 <br>
	 * Create on:2008-12-29  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	*/
	public InspectionRequisitionDO getInspectionRequisition(Long inspectionRequisitionId){
		String hql = " from InspectionRequisitionDO INS where INS.keyword = ? ";
		Query query = this.createQuery(hql, inspectionRequisitionId);
		List<InspectionRequisitionDO> list = query.list();

		if(list.size() > 0) {
			return  list.get(0);
		}else {
			return null;
		}
	}

	/** 功能描述: 根据许可证ID获得装修验收对象 <br>
	 * @param licenceId - 许可证ID
	 * @return
	 * Create author:许心炜 <br>
	 * Create on:2008-12-29  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	*/
	public InspectionRequisitionDO getInspectionRequisitionByLicenceId(Long licenceId) {
		String hql = " from InspectionRequisitionDO INS where INS.renovateLicence.keyword = ? ";
		Query query = this.createQuery(hql, licenceId);
		List<InspectionRequisitionDO> list = query.list();
		if(list.size() > 0) {
			return  list.get(0);
		}else {
			return null;
		}

	}
}
